/*-------------------<-- Start of Description-->---------------------\
| Write the variables from the input data set to an opened excel     |
| spread sheet;                                                      |
|---------------------<-- End of Description-->----------------------|
|--------------------------------------------------------------------|
|------------<-- Start of Files or Arguments Needed-->---------------|
| Arguments:                                                         |
|    indata = input dataset;                                         |
|    vars = create variable names for each column;                   |
|    shtname = the name of the sheet you want to read;               |
| Note: If variables are given, then the function will write the     |
|     variable names on the 1st row (in the order given accordingly),|
|     and the values of the variables into the corresponding columns;|
|     otherwise, the function will write all variables of the input  |
|     data set into the corresponding columns as they occur in the   |
|     dataset;                                                       |
| Note: if you want to arrange the order of the variables, then you  |
|     have to arrange them in the form  of "vars=var format.|", but I|
|     suggest you arrange the variables in the dataset, then apply   |
|     this function;                                                 |
|-------------<-- End of Files or Arguments Needed-->----------------|
|--------------------------------------------------------------------|
|------------------<-- Start of Files Created-->---------------------|
| Example: %excelwrite(indata=followup, vars=STUDY $10.|PT $10.|     |
|                CPEVENT $10.|VISIT |SUBEVE |ACTEVENT |QUALIFYV |    |
|                REPEATSN |PIDENT |INVSITE |INV |HOSP |CITY |        |
|                MDTNAME |VISDT |PHD |DEVTST)                        |
| Usage:   %excelwrite(indata=,vars=,shtname=sheet1);                |
\-------------------<-- End of Files Created-->---------------------*/
%macro excelwrite(indata=,vars=,shtname=sheet1);
/*--------------------------------------------\
| Author:   Duo Zhou;                         |
| Created:  2-5-2001 11:00pm;                 |
| Modified: 12-25-2001 4:40pm;                |
| Purpose:  Write the contents of a dataset to|
|           an excel spread sheet;            |
\--------------------------------------------*/
%local nvariables nobservations _i_ _j_ _k_;
%if &shtname eq %then %let shtname=sheet1;
%let dset=&indata; %let dsid=%sysfunc(open(&dset));
%if &dsid %then %do;
   /*%put &vars;*/
   %if (%qscan(&vars,1,%str( )) ne) %then %do;
      /*%put 2nd if loop, current data set is &dsid;*/
      %let nobservations=%sysfunc(attrn(&dsid,NOBS));
      %let rc=%sysfunc(close(&dsid));
      %let count=1;
      %let var&count=%qscan(&vars, &count, %str(,()|));
      %let varname&count=%qscan(&&var&count,1,%str(|,() ));
      %do %while(%length(&&var&count) gt 0);
         %let count=%eval(&count+1);
         %let var&count=%qscan(&vars, &count, %str(,|()));
         %let varname&count=%qscan(&&var&count,1,%str(|,() ));
      %end;
      %let nvariables =%eval(&count-1);
      %let rows=%eval(&nobservations+1);
      %let numoftabs=%eval(&nvariables-1);
      filename excelsyl dde "excel|&shtname.!r1c1:r&rows.c&nvariables" notab LRECL=1048576;
      data _null_;
         file excelsyl;
         set &indata;
         if _n_=1 then do;
            put %do _k_=1 %to &numoftabs; "&&varname&_k_" '09'x %end; "&&varname&nvariables";
         end;
         put %do _k_=1 %to &numoftabs; &&var&_k_ '09'x %end; &&var&nvariables;
      run;
   %end;
   %else %do;
      %let nvariables=%sysfunc(attrn(&dsid,NVARS)); %let nobservations=%sysfunc(attrn(&dsid,NOBS));
      /*%put num of observation is &nobservations, num of variables is &nvariables;*/
      %let rows=%eval(&nobservations+1); %let numoftabs=%eval(&nvariables-1);
      %do _i_=1 %to &nvariables; %let var&_i_=%sysfunc(varname(&dsid,&_i_)); %end;
      %let rc=%sysfunc(close(&dsid));
      filename excelsyl dde "excel|&shtname.!r1c1:r&rows.c&nvariables" notab LRECL=1048576;
      data _null_;
         file excelsyl;
         set &indata;
         if _n_=1 then do;
            put %do _k_=1 %to &numoftabs; "&&var&_k_" '09'x %end; "&&var&nvariables";
         end;
         put %do _k_=1 %to &numoftabs; &&var&_k_ '09'x %end; &&var&nvariables;
      run;
   %end;
%end;
%else %put ==> Alert! Open for data set "&dset" failed. ;
%mend excelwrite;